package org.samples.util;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.samples.model.ExamUser;

public class ExcelUtils {

	public static String OFFICE_EXCEL_2003_POSTFIX = ".xls";
	public static String OFFICE_EXCEL_2007_POSTFIX = ".xlsx";
	
	public static Workbook openWorkBook(InputStream inputStream, String postfix){
		Workbook workbook = null;
		try {
			if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
				workbook = new HSSFWorkbook(inputStream);
			}
			else if(OFFICE_EXCEL_2007_POSTFIX.equals(postfix)){
				workbook = new XSSFWorkbook(inputStream);
			}
		} catch (IOException e) {
			if(e instanceof NotOLE2FileException)
			e.printStackTrace();
		}
		return workbook;
	}
	
	public static List<ExamUser> getUserListByExcel(Workbook workbook){
		String GENDER_MALE_STRING = "男", GENDER_FEMALE_STRING = "女";
		Integer GENDER_MALE_INTEGER = 0, GENDER_FEMALE_INTEGER = 1;
		List<ExamUser> resultList = new ArrayList<ExamUser>();
		Sheet sheet = workbook.getSheetAt(0);
		int maxCellNumber = 0;
		int maxRowNumber = sheet.getLastRowNum();
		ExamUser user = null;
		for(int currentRowNumber = 1; currentRowNumber <= maxRowNumber; currentRowNumber++){
			Row row = sheet.getRow(currentRowNumber);
			maxCellNumber = row.getLastCellNum();
			user = new ExamUser();
			for(int currentCellNumber = 0; currentCellNumber <= maxCellNumber; currentCellNumber++){
				Cell cell = row.getCell(currentCellNumber);
				switch(currentCellNumber){
					case 0:
						user.setUserId(getValue(cell));
						break;
					case 1:
						user.setUserName(getValue(cell));
						break;
					case 2:
						String decodingPassword = getValue(cell);
						user.setPassword(MD5Utils.getMD5Code(decodingPassword));
						break;
					case 3:
						user.setTel(getValue(cell));
						break;
					case 4:
						user.setEmail(getValue(cell));
						break;
					case 5:
						user.setAddress(getValue(cell));
						break;
					case 6:
						user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue()));
						break;
					case 7:
						String gender = getValue(cell);
						if(GENDER_MALE_STRING.equals(gender)){
							user.setGender(GENDER_MALE_INTEGER);
						}else if(GENDER_FEMALE_STRING.equals(gender)){
							user.setGender(GENDER_FEMALE_INTEGER);
						}
						break;
					case 8:
						user.setLastLockTime(cell.getDateCellValue());
						break;
					case 9:
						user.setLoginFailCount(Integer.valueOf(getValue(cell)));
						break;
				}
			}
			resultList.add(user);
		}
		return resultList;
	}
	
	@SuppressWarnings("static-access")
	private static String getValue(Cell cell){
		if(cell == null) return "";
		if(cell.getCellType() == cell.CELL_TYPE_BOOLEAN){
			return String.valueOf(cell.getBooleanCellValue());
		}else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){
			DecimalFormat df = new DecimalFormat("0");
			return df.format(cell.getNumericCellValue());
		}else{
			return String.valueOf(cell.getStringCellValue());
		}
	}
	
}
